/**
 * 
 * @Project le cOeur - Data Virtualization
 * @Date 08.12.2012
 * @Author Aebi Pascal, Kocher Marc
 * @School BFH-TI Biel, Bachelor Thesis
 *
 */

package main.framework.connector;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import main.framework.setting.Config;

/**
 * This class allows to connect/disconnect to a Microsoft SQL Server database and to execute a desired query.
 */
public class SQLConnector {

	private Connection connection;
	
	/**
	 * Establishes a connection to a database (over the JDBC interface).
	 * 
	 * @throws ClassNotFoundException If the class cannot be located.
	 * @throws SQLException If a database access error occurs.
	 */
	public void connect() throws ClassNotFoundException, SQLException {
		Class.forName ("net.sourceforge.jtds.jdbc.Driver");
		this.connection = DriverManager.getConnection("jdbc:jtds:sqlserver://" + Config.DB_HOST + ";DatabaseName=" + Config.DB_NAME, Config.DB_USER, Config.DB_USER_PW);
	}
	
	/**
	 * Executes a query for any kind of operations on the database.
	 * 
	 * @param query The desired query to execute.
	 * @throws SQLException If a database access error occurs, this method is called on a closed Statement, the method is called on a PreparedStatement or CallableStatement.
	 */
	public void executeQuery(String query) throws SQLException {
		Statement statement = this.connection.createStatement();
		statement.execute(query);
	}

	/**
	 * Executes a query for getting data from the database.
	 * 
	 * @param query The desired query to execute.
	 * @return A list containing a string array with the results of the select query.
	 * @throws SQLException If a database access error occurs during a method execution.
	 */
	public List<String[]> executeGetQuery(String query) throws SQLException {
		Statement statement = this.connection.createStatement();
		ResultSet resultSet = statement.executeQuery(query);
		int columnCount = resultSet.getMetaData().getColumnCount();
		List<String[]> rows = new ArrayList<>();
		while(resultSet.next()){
			String[] row = new String[columnCount];
			for (int i = 0; i <= columnCount; i++) {
				row[i-1] = resultSet.getString(i);
			}
			rows.add(row);
        }
		return rows;
	}
	
	/**
	 * Disconnect the connection to a database.
	 * 
	 * @throws SQLException If a database access error occurs.
	 */
	public void disconnect() throws SQLException {
		this.connection.close();
	}

}
